from traffic_analysis.config import db_connection
import aiomysql
import asyncio

# 同步版本的函数
def get_all_accidents():
    conn = db_connection.DatabaseConnectionPool.get_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM accidents')
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result

# 异步版本的函数
async def get_all_accidents_async():
    conn = await aiomysql.connect(host='localhost', port=3306, user='root', password='1234', db='traffic')
    cursor = await conn.cursor()
    await cursor.execute('SELECT * FROM accidents')
    result = await cursor.fetchall()
    await cursor.close()  # 使用 await 关闭游标
    conn.close()
    return result

# 异步版本的函数
async def get_all_traffic_async():
    conn = await aiomysql.connect(host='localhost', port=3306, user='root', password='1234', db='traffic')
    cursor = await conn.cursor()
    await cursor.execute('SELECT * FROM traffic')
    result = await cursor.fetchall()
    await cursor.close()  # 使用 await 关闭游标
    conn.close()
    return result

# 异步版本的函数，合并 accidents 和 traffic 表
async def get_accidents_and_traffic_async():
    conn = await aiomysql.connect(host='localhost', port=3306, user='root', password='1234', db='traffic')
    cursor = await conn.cursor()
    # 以 traffic 表为主表，根据 bmcode 进行连接
    query = '''
    SELECT t.*, a.*
    FROM traffic t
    LEFT JOIN accidents a ON t.bmcode = a.bmcode
    '''
    await cursor.execute(query)
    result = await cursor.fetchall()
    await cursor.close()
    conn.close()
    return result

# 单独获取事故数据
async def fetch_accidents():
    accidents = await get_all_accidents_async()
    print("Accidents:", accidents)

# 单独获取交通数据
async def fetch_traffic():
    traffic = await get_all_traffic_async()
    print("Traffic:", traffic)

# 单独获取交通数据
async def fetch_accidents_and_traffic():
    data = await get_accidents_and_traffic_async()
    print("Traffic:", data)

# 程序入口
if __name__ == "__main__":
    # # 单独获取事故数据
    # asyncio.run(fetch_accidents())
    #
    # # 单独获取交通数据
    # asyncio.run(fetch_traffic())

    # 单独获取交通数据
    asyncio.run(fetch_accidents_and_traffic())